﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;


public partial class StortedProc : System.Web.UI.Page
{
    DataTable dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        dt = getTableDT();
        GridView1.DataSource = dt;
        GridView1.DataBind();
        CreateSelectString();
        CreateInsertString();
        CreateUpdateString();
        CreateDeleteString();
    }
    public DataTable getTableDT()
    {
        SqlConnection conn = new SqlConnection(Session["CONNSTR"].ToString());
        string cmdText = "SELECT  a.COLUMN_NAME as 欄位名稱, a.DATA_TYPE as 資料型別 , a.CHARACTER_MAXIMUM_LENGTH as 長度, a.IS_NULLABLE as [可null] FROM INFORMATION_SCHEMA.COLUMNS a WHERE table_name=@table_name ";
        SqlCommand cmd = new SqlCommand(cmdText, conn);
        cmd.Parameters.AddWithValue("@table_name", this.uc_Tables.SelectedValue);
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(ds);
        return ds.Tables[0];
    }

    public void CreateSelectString()
    {
        string parStr = "";
        string clmStr = "";
        string whereStr = "";

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            //參數
            if (dt.Rows[i][3].ToString().ToUpper().Equals("NO"))
            {
                parStr += "\n\t";
                parStr += string.Format("  @{0} {1},",
                                        dt.Rows[i][0],
                                        (dt.Rows[i][2] == DBNull.Value) ?
                                            //dt.Rows[i][1] :
                                            "nvarchar(20)" :
                                            "nvarchar(" + dt.Rows[i][2] + ")");
            }
            if (i == (dt.Rows.Count - 1) && parStr.Trim().Length > 1)
                parStr = parStr.Substring(0, parStr.Length - 1);

            //欄位
            clmStr += "\n\t";
            clmStr += (dt.Rows[i][2] == DBNull.Value) ?
                (i == 0) ? dt.Rows[i][0].ToString() : " , " + dt.Rows[i][0].ToString() :
                (i == 0) ? string.Format(" rtrim({0}) as {0}", dt.Rows[i][0]) :
                    string.Format(" , rtrim({0}) as {0}", dt.Rows[i][0]);

            //where
            whereStr += (dt.Rows[i][3].ToString().ToUpper().Equals("NO")) ?
               "\n\t" + ((whereStr.Trim().Length < 6) ? string.Format(" {0} = @{0}", dt.Rows[i][0]) : string.Format(" and {0} = @{0}", dt.Rows[i][0])) :
                        "";
        }

        tbx_Select.Text = tbx_Select_Temp.Text;
        tbx_Select.Text = tbx_Select.Text.Replace("##tablename##", uc_Tables.SelectedValue);
        tbx_Select.Text = tbx_Select.Text.Replace("##parStr##", parStr);
        tbx_Select.Text = tbx_Select.Text.Replace("##clmStr##", clmStr);
        if (whereStr.Trim().Length > 0)
            tbx_Select.Text = tbx_Select.Text.Replace("##whereStr##", whereStr);
        else
            tbx_Select.Text = tbx_Select.Text.Replace("where", "");

    }

    public void CreateInsertString()
    {
        string parStr = "";
        string clmStr = "";
        string valStr = "";

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            //參數

            parStr += "\n\t";
            parStr += string.Format("  @{0} {1},",
                                    dt.Rows[i][0],
                                    (dt.Rows[i][2] == DBNull.Value) ?
                                        //dt.Rows[i][1] :
                                        "nvarchar(20)" :
                                        "nvarchar(" + dt.Rows[i][2] + ")");

            if (i == (dt.Rows.Count - 1) && parStr.Trim().Length > 1)
                parStr = parStr.Substring(0, parStr.Length - 1);

            //欄位
            clmStr += "\n\t";
            clmStr += (i == 0) ? dt.Rows[i][0].ToString() : " , " + dt.Rows[i][0].ToString();

            //values
            valStr += "\n\t";
            valStr += (i == 0) ? "@" + dt.Rows[i][0].ToString() : " , @" + dt.Rows[i][0].ToString();

        }

        tbx_Insert.Text = tbx_Insert_Temp.Text;
        tbx_Insert.Text = tbx_Insert.Text.Replace("##tablename##", uc_Tables.SelectedValue);
        tbx_Insert.Text = tbx_Insert.Text.Replace("##parStr##", parStr);
        tbx_Insert.Text = tbx_Insert.Text.Replace("##clmStr##", clmStr);
        tbx_Insert.Text = tbx_Insert.Text.Replace("##valStr##", valStr);

    }

    public void CreateUpdateString()
    {
        string parStr = "";
        string clmStr = "";
        string whereStr = "";

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            //參數
            parStr += "\n\t";
            parStr += string.Format("  @{0} {1},",
                                    dt.Rows[i][0],
                                    (dt.Rows[i][2] == DBNull.Value) ?
                                        //dt.Rows[i][1] :
                                        "nvarchar(20)" :
                                        "nvarchar(" + dt.Rows[i][2] + ")");
            if (i == (dt.Rows.Count - 1) && parStr.Trim().Length > 1)
                parStr = parStr.Substring(0, parStr.Length - 1);

            //欄位
            clmStr += (dt.Rows[i][3].ToString().ToUpper().Equals("NO")) ?
                "":
                (clmStr.Trim().Length == 0) ? string.Format("\n\t {0} = @{0}", dt.Rows[i][0]) :
                    string.Format("\n\t , {0} = @{0}", dt.Rows[i][0]);

            //where
            whereStr += (dt.Rows[i][3].ToString().ToUpper().Equals("NO")) ?
               "\n\t" + ((whereStr.Trim().Length < 6) ? string.Format(" {0} = @{0}", dt.Rows[i][0]) : string.Format(" and {0} = @{0}", dt.Rows[i][0])) :
                        "";

            tbx_Update.Text = tbx_Update_Temp.Text;
            tbx_Update.Text = tbx_Update.Text.Replace("##tablename##", uc_Tables.SelectedValue);
            tbx_Update.Text = tbx_Update.Text.Replace("##parStr##", parStr);
            tbx_Update.Text = tbx_Update.Text.Replace("##clmStr##", clmStr);
            tbx_Update.Text = tbx_Update.Text.Replace("##whereStr##", whereStr);
        }
    }

    public void CreateDeleteString()
    {
        string parStr = "";
        string clmStr = "";
        string whereStr = "";

        for (int i = 0; i < dt.Rows.Count; i++)
        {
            //參數
            if (dt.Rows[i][3].ToString().ToUpper().Equals("NO"))
            {
                parStr += "\n\t";
                parStr += string.Format("  @{0} {1},",
                                        dt.Rows[i][0],
                                        (dt.Rows[i][2] == DBNull.Value) ?
                                            //dt.Rows[i][1] :
                                            "nvarchar(20)" :
                                            "nvarchar(" + dt.Rows[i][2] + ")");
            }
            if (i == (dt.Rows.Count - 1) && parStr.Trim().Length > 1)
                parStr = parStr.Substring(0, parStr.Length - 1);

            ////欄位
            //clmStr += "\n\t";
            //clmStr += (dt.Rows[i][2] == DBNull.Value) ?
            //    (i == 0) ? dt.Rows[i][0].ToString() : " , " + dt.Rows[i][0].ToString() :
            //    (i == 0) ? string.Format(" {0} ", dt.Rows[i][0]) :
            //        string.Format(" , {0}", dt.Rows[i][0]);

            //where
            whereStr += (dt.Rows[i][3].ToString().ToUpper().Equals("NO")) ?
               "\n\t" + ((whereStr.Trim().Length < 6) ? string.Format(" {0} = @{0}", dt.Rows[i][0]) : string.Format(" and {0} = @{0}", dt.Rows[i][0])) :
                        "";

            tbx_Delete.Text = tbx_Delete_Temp.Text;
            tbx_Delete.Text = tbx_Delete.Text.Replace("##tablename##", uc_Tables.SelectedValue);
            tbx_Delete.Text = tbx_Delete.Text.Replace("##parStr##", parStr);
            //tbx_Delete.Text = tbx_Delete.Text.Replace("##clmStr##", clmStr);
            tbx_Delete.Text = tbx_Delete.Text.Replace("##whereStr##", whereStr);
        }
    }

    protected void btn_ExecSQL_Click(object sender, EventArgs e)
    {
        string cmdStr = (pnlScript.FindControl((sender as Button).CommandArgument) as TextBox).Text;
        SqlConnection conn = new SqlConnection(Session["CONNSTR"].ToString());
        SqlCommand cmd = new SqlCommand(cmdStr, conn);
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            Response.Write("<script>alert('ok');</script>");
        }
        catch (Exception ex)
        {
            Response.Write("<script>alert('錯誤。"+ex.Message+"');</script>");        
        }
        finally
        {
            conn.Close();
        }    
    }
}
